package org.example;

import java.sql.*;

/**
 *  jdbc大批量插入数据,插入2千万条,仅实际验证batch_size设置为1万时,插入速度最快
 */
public class AppBatchInsertDemo {

    // 修改为你的数据库连接信息
    static final String URL = "jdbc:mysql://localhost:13306/test1?rewriteBatchedStatements=true&useSSL=false&serverTimezone=Asia/Shanghai";
    static final String USER = "root";
    static final String PASSWORD = "scyy";

    static final int TOTAL = 20_000_000;
    static final int BATCH_SIZE = 10_000;

    public static void main(String[] args) {

        System.out.println("批量化插入数据库操作开始了.");

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            conn.setAutoCommit(false); // 关闭自动提交，加快插入

// 自动建表（已存在则忽略）
//            try (Statement stmt = conn.createStatement()) {
//                stmt.execute("CREATE TABLE if not exists `t_users` (\n" +
//                        "                           `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
//                        "                           `name` varchar(2048) DEFAULT NULL,\n" +
//                        "                           `gender` varchar(1024) DEFAULT NULL,\n" +
//                        "                           `age` int(11) DEFAULT NULL,\n" +
//                        "                           PRIMARY KEY (`id`)\n" +
//                        ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
//            }

            String sql = "INSERT INTO t_users (id, name, gender, age) VALUES (null, ?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                long start = System.currentTimeMillis();

                for (int i = 1; i <= TOTAL; i++) {
//                    ps.setLong(1, i);
                    ps.setString(1, "scyaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa" + i);
                    ps.setString(2, "maleeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee");
                    ps.setInt(3, 33);
                    ps.addBatch();

                    if (i % BATCH_SIZE == 0) {
                        ps.executeBatch();
                        conn.commit();
                        ps.clearBatch();
                        System.out.println("已插入: " + i + " 条");
                    }
                }
                // 插入剩余数据
                ps.executeBatch();
                conn.commit();

                long end = System.currentTimeMillis();
                System.out.println("✅ 插入完成，共耗时: " + (end - start) / 1000.0 + " 秒");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
